﻿// Learn more about F# at http://fsharp.net
// Returns some dataload SQL for area mapping.
open System
open System.IO
open LumenWorks.Framework.IO.Csv
open System.Data.Linq
open System.Data.Linq.Mapping
open System.Data.SQLite 
open System.Collections.Generic
open System.Drawing
open System.Text
open System.Windows.Forms

//Class que reprenta um professor
[<Class>]
type Teacher (cod:string, name:string, degree:string, curse:string, catgory:string, regime:string, year:string, type_establishment:string, establishment:string) =
    let mutable cod = cod.Replace("'","''") //Substitui o carater ' por '' nos atributos 
    let mutable name = name.Replace("'","''")
    let mutable degree = degree.Replace("'","''")
    let mutable curse = curse.Replace("'","''")
    let mutable catgory = catgory.Replace("'","''")
    let mutable regime = regime.Replace("'","''")
    let mutable year = year.Replace("'","''")
    let mutable type_establishment = type_establishment.Replace("'","''")
    let mutable establihment = establishment.Replace("'","''")


    member this.Codigo with get() = cod // getters and setters
    member this.Nome with get() =  name
    member this.Grau with get() =  degree
    member this.Curso with get() =  curse
    member this.Categoria with get() =  catgory
    member this.Regime with get() =  regime
    member this.Ano with get() =  year
    member this.Tipo_de_establecimento with get() =  type_establishment
    member this.Nome_de_establecimento with get() =  establishment

    override this.ToString() =         
        System.String.Format(@"'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}'",
            cod, name, degree, curse, catgory, regime, year, type_establishment, establishment)
//Class que lida com a base de dados
type SQLHandler(db:string) =
    let DataBase = db
    let connString = System.String.Format("Data Source={0};UTF8Encoding=True;Version=3", DataBase) // string de ligacao
    let conn = new SQLiteConnection(connString) // nova ligacao
    
    //cria tabela na base de dados
    member s.createTable(table:string) = 
            conn.Open()
            use dbTrans = conn.BeginTransaction()
            let cmd = conn.CreateCommand() 
            cmd.CommandText <- String.Format(@"CREATE TABLE if not exists ""{0}""(cod, name, degree, curse, catgory, regime, year, type_establishment, establishment);",table)
            cmd.ExecuteNonQuery() |>ignore
            dbTrans.Commit()    
            conn.Close()       

    //Insere lista de professores na base de dados
    member s.insertData(table:string, data:List<Teacher>) =
        conn.Open()
        use dbTrans = conn.BeginTransaction()
        let cmd = conn.CreateCommand()
         
        for teacher in data do
            cmd.CommandText <- String.Format("INSERT INTO \"{0}\"values({1});",table,teacher.ToString())
            cmd.ExecuteNonQuery()|>ignore
        dbTrans.Commit()
        conn.Close()
    
    //retorna Lista de professores na base de dados
    member s.getFullData(table:string) =
        conn.Open()
        use dbTrans = conn.BeginTransaction()
        let cmd = conn.CreateCommand()          
        cmd.CommandText <- String.Format("SELECT * FROM \"{0}\";",table)
        let reg = cmd.ExecuteReader()
        
        let lista = new List<Teacher>()
        while(reg.Read())do
            lista.Add(new Teacher(reg.GetString(0),
                                  reg.GetString(1),
                                  reg.GetString(2),
                                  reg.GetString(3),
                                  reg.GetString(4),
                                  reg.GetString(5),
                                  reg.GetString(6),
                                  reg.GetString(7),
                                  reg.GetString(8)))   
        dbTrans.Commit()|>ignore
        conn.Close()|>ignore
        lista

//calss que lida com o ficheiro csv
type csvHandler(filename:string) =
    let filename = filename
    let data = new List<Teacher>()
    let csv = new CsvReader(new StreamReader(filename),true);
    
    //Le os professores do csv
    member s.getTeachers =
        for row in csv do
                data.Add(new Teacher(row.[0],row.[1],csv.[2],csv.[3],csv.[4],csv.[5],csv.[6],csv.[7],csv.[8]))
        data
//Class que herda de Form e permite ver as estatisticas
type Statistics(db:string, table:string, cat:Boolean) as seform2 =
    inherit Form()
    let sql = new SQLHandler(db)
    let teachers = sql.getFullData(table)
    let statType = cat
    let panel = new Panel()
    let list1 = new ListBox()
    let list2 = new ListBox()
    let list3 = new ListBox()
    let list4 = new ListBox()
    let list5 = new DataGridView()

    let mutable year = ""
    let mutable establishment = ""
    let mutable catOrDegree = ""

    do seform2.getYears 
    
    //Preenche listade anos
    member this.getYears = 
        for x in teachers do
            if not (list1.Items.Contains(x.Ano)) then
                list1.Items.Add(x.Ano)|>ignore

    //Preeenche lista de establecimentos
    member this.getEstablisment =
        for x in teachers do
            if not (list2.Items.Contains(x.Nome_de_establecimento)) && (x.Ano = year) then
                list2.Items.Add(x.Nome_de_establecimento)|>ignore  

    //preenche lista de categorias ou graus    
    member this.getCatOrDegree = 
        for x in teachers do
            if not (list3.Items.Contains(if(statType)then x.Categoria else x.Grau)) 
            && (x.Ano = year) 
            && (x.Nome_de_establecimento = establishment) then
                list3.Items.Add(if(statType)then x.Categoria else x.Grau)|>ignore 
                 
    //preenche grelha
    member this.fillGrid =
        let lista = new List<Teacher>()
        for x in teachers do
            if  (if(statType)then (x.Categoria = catOrDegree)  else (x.Grau = catOrDegree)) 
            && (x.Ano = year) 
            && (x.Nome_de_establecimento = establishment) then
                lista.Add(x)
        list5.DataSource <- lista

    //Preeenche lista de estatisticas
    member this.setStat =        
        for y in list1.Items do
            let mutable count = 0
            for x in teachers do
                if (if(statType)then (x.Categoria = catOrDegree)  else (x.Grau = catOrDegree)) 
                && (x.Nome_de_establecimento = establishment) && (x.Ano = y.ToString()) then
                    count <- count + 1
            list4.Items.Add(String.Format("{0}->{1}",y ,count))|>ignore

    //evento de click na lista de anos                                    
    member this.List1_Click(sender : System.Object, e : EventArgs) =
        if not (list1.SelectedItem = null)then
            year <- (list1.SelectedItem.ToString())        
            list2.Items.Clear()
            this.getEstablisment

    //evento de click na lista de establecimentos   
    member this.List2_Click(sender : System.Object, e : EventArgs) =
        if not (list2.SelectedItem = null)then
            establishment <- (list2.SelectedItem.ToString())        
            list3.Items.Clear()
            this.getCatOrDegree

    //evento de click na lista de categorias ou graus   
    member this.List3_Click(sender : System.Object, e : EventArgs) =
        if not (list3.SelectedItem = null)then
            catOrDegree <- list3.SelectedItem.ToString()      
            this.fillGrid  
            list4.Items.Clear()
            this.setStat

    //Mostra e adiciona items á form             
    member this.show =
        this.Width  <- 900
        this.Height <- 600
        this.Visible <- true 
        this.WindowState <- FormWindowState.Maximized
        this.Text <- "Statistics"
            
        panel.Dock <- DockStyle.Top
        panel.Height <- this.Height / 2   
    
        list1.Width <- this.Width / 4
        list1.Click.AddHandler(new System.EventHandler 
            (fun s e -> this.List1_Click(s, e)))
        list1.Dock <- DockStyle.Left
              
        
        list2.Width <- this.Width / 4
        list2.Click.AddHandler(new System.EventHandler 
            (fun s e -> this.List2_Click(s, e)))
        list2.Dock <- DockStyle.Left
        
            
        list3.Width <- this.Width / 4
        list3.Click.AddHandler(new System.EventHandler 
            (fun s e -> this.List3_Click(s, e)))
        list3.Dock <- DockStyle.Left
       
    
        list4.Width <- this.Width / 4
        list4.Dock <- DockStyle.Fill        
    
        list5.Dock <- DockStyle.Fill

        panel.Controls.Add(list4)
        panel.Controls.Add(list3)
        panel.Controls.Add(list2)
        panel.Controls.Add(list1)
        
        this.Controls.Add(list5)
        this.Controls.Add(panel)

// Main Execution, Class que herda de Form
type Principal() as form =
    inherit Form()
    let panel = new Panel()
    let op = new Button()
    let dump = new Button()
    let stat = new Button()
    let cat = new RadioButton()
    let deg = new RadioButton()
    let grid = new DataGrid()
    
    let panel2 = new Panel()
    let fileLabel = new Label()
    let dbLabel = new Label()
    let tableLabel = new Label()
    let db = new TextBox()
    let table = new TextBox()

    let mutable filename = "" //nome do ficheiro
    let mutable teachers = new List<Teacher>() //lista de professores
    
    do form.createGUI
    
    //retorna se o radiobutton de categoria esta selecionado
    member this.getStatType = cat.Checked

    //Mostar caixa de dialogo de ficheiros 
    member this.openFile_Click(sender : System.Object, e : EventArgs) =  
        let mutable openFileDialog1 = new OpenFileDialog()

        openFileDialog1.InitialDirectory <- "." 
        openFileDialog1.Filter <- "CSV files (*.CSV)|*.csv"
        openFileDialog1.FilterIndex <- 2 
        openFileDialog1.RestoreDirectory <- true 

        if (openFileDialog1.ShowDialog() = DialogResult.OK) then        
            filename <- openFileDialog1.FileName  
            this.fillGrid|>ignore
            dump.Enabled <- true
            fileLabel.Text <- "File: " + filename
    
    //evento de click do buttao dump   
    member this.dump_Click(sender : System.Object, e : EventArgs) = 
        try
            let mutable sql = new SQLHandler(db.Text)
            sql.createTable(table.Text)    
            sql.insertData(table.Text, teachers)  
            MessageBox.Show("File dump complete!")|>ignore
        with
            |exn -> MessageBox.Show("Error!")|>ignore             

    //evento de click do buttao de estatistica        
    member this.statistics_Click(sender : System.Object, e : EventArgs) =  
        try
            let form2 = new Statistics(db.Text, table.Text,this.getStatType)
            form2.show        
        with
        | exn -> MessageBox.Show "Error, no data base found!"|>ignore
    //preenche grelha a partir do csv
    member this.fillGrid =
        let csv = new csvHandler(filename)
        teachers <- csv.getTeachers
        grid.DataSource <- teachers 
    
    //mostra e preenche items na form
    member this.createGUI =
        this.Width  <- 900
        this.Height <- 600
        this.Visible <- true
        this.WindowState <- FormWindowState.Maximized 
        this.Text <- "f#"

        panel.Dock <- DockStyle.Top
        panel.Height <- form.Height / 15
        
        op.Text <- "Open File"
        op.Click.AddHandler(new System.EventHandler 
            (fun s e -> this.openFile_Click(s, e)))

        op.Dock <- DockStyle.Left
    
        dump.Text <- "Dump data"
        dump.Enabled <- false
        dump.Click.AddHandler(new System.EventHandler 
            (fun s e -> this.dump_Click(s, e)))
        dump.Dock <- DockStyle.Left
    
        stat.Text <- "Statistics"
        stat.Click.AddHandler(new System.EventHandler 
            (fun s e -> this.statistics_Click(s, e)))
        stat.Dock <- DockStyle.Left
    
        cat.Text <- "Catgory"
        cat.Checked <- true
        cat.Dock <- DockStyle.Left
   
        deg.Text <- "Degree"
        deg.Dock <- DockStyle.Left  

        panel2.Dock <- DockStyle.Top
        panel2.Height <- form.Height / 15

        fileLabel.Text <- "File: "
        fileLabel.Dock <- DockStyle.Top

        table.Dock <- DockStyle.Left
        table.Text <- "reb10"

        tableLabel.Text <- "Table: "
        tableLabel.Dock <- DockStyle.Left
           
        db.Dock <- DockStyle.Left
        db.Text <- "reb"

        dbLabel.Text <- "Data base: "
        dbLabel.Dock <- DockStyle.Left

        grid.Dock <- DockStyle.Fill
        
        panel2.Controls.AddRange([|(table:> Control);
                                  (tableLabel:> Control);
                                  (db:> Control);
                                  (dbLabel:> Control);
                                  (fileLabel:> Control);|])

        panel.Controls.AddRange([|(deg:> Control);
                                (cat:> Control);
                                (stat:> Control);
                                (dump:> Control);
                                (op:> Control)|])

        this.Controls.AddRange([|(grid:> Control);
                                (panel2:> Control);
                                (panel:> Control)|])    

[<STAThread()>] 
do Application.Run(new Principal())

